﻿using Coldairarrow.Util.Excel.Model;
using Coldairarrow.Util.Excel.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using Quartz.Impl.Triggers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Coldairarrow.Util.Excel.Analyze
{
    public class BaseDataAnalyzer
    {
        /// <summary>
        /// 根据规则配置取得excel的数据集
        /// </summary>
        /// <param name="parseExcelRuleUtil"></param>
        /// <param name="workbook"></param>
        /// <returns></returns>
        protected DataSet GetAnalyzeDataSet(ParseExcelRuleUtil parseExcelRuleUtil, IWorkbook workbook)
        {
            string sheetName = string.Empty;
            if (workbook == null)
            {
                return null;
            }
            else
            {
                DataSet dataSet = new DataSet();

                for (int index = 0; index < workbook.NumberOfSheets; index++)
                {
                    try
                    {
                        ISheet sheet = workbook.GetSheetAt(index);
                        sheetName = sheet.SheetName;
                        if (sheet != null)
                        {
                            SheetRule rule = parseExcelRuleUtil.GetRuleBySheetName(sheet.SheetName);
                            if (rule == null)
                            {
                                //rule = parseExcelRuleUtil.GetRuleBySheetIndex(index);
                                //if (rule == null)
                                //{
                                //    continue;
                                //}
                                continue;
                            }

                            List<DataTable> tableList = this.GetDataTable(sheet, rule);
                            if (tableList!=null && tableList.Count>0)
                            {
                                DataTable table = tableList[0];
                                table.TableName = (table.TableName.Replace(")", "）").Replace("(", "（"));
                                if (!dataSet.Tables.Contains(table.TableName))
                                {
                                    //table.setTag(rule);
                                    dataSet.Tables.Add(table);
                                    if (tableList.Count > 1)
                                    {
                                        tableList[1].TableName = GlobalUtil.GetErrorTableName(table.TableName);
                                        dataSet.Tables.Add(tableList[1]);
                                    }
                                }
                            }
                        }
                    }
                    catch (Exception var10)
                    {
                        throw new Exception("表:"+sheetName + ",找不到第n列:"+ var10);
                       // throw var10;
                    }
                }

                return dataSet;
            }
        }

        /// <summary>
        ///取得excel sheet 字符串内容
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="startRow"></param>
        /// <param name="endRow"></param>
        /// <returns></returns>
        public List<List<string>> GetSheetContent(ISheet sheet, int startRow, int endRow)
        {
            List<List<string>> sheetContent = new List<List<string>>();
            int start = (startRow >= 0) ? startRow : 0;
            int end =  endRow >= 0 ? endRow : sheet.PhysicalNumberOfRows;

            for (int j = start; j < end; ++j)
            {
                IRow row = sheet.GetRow(j);
                int cellCount = row.PhysicalNumberOfCells;
                List<string> cellContents = new List<string>();

                for (int k = 0; k < cellCount; ++k)
                {
                    ICell cell = row.GetCell(k);
                    String value = ExcelUtil.GetCellStringValue(cell);
                    cellContents.Add(value);
                }
                sheetContent.Add(cellContents);
            }
            return sheetContent;
        }

        /// <summary>
        /// 取得excel 所有sheet页字符串内容
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="startRowDictionary"></param>
        /// <param name="endRowDictionary"></param>
        /// <returns></returns>
        public Dictionary<string, List<List<string>>> GetCommonList(IWorkbook workbook, Dictionary<string, int> startRowDictionary, Dictionary<string, int> endRowDictionary)
        {
            Dictionary<string, List<List<string>>> result = new Dictionary<string, List<List<string>>>();
            if (workbook == null)
            {
                return result;
            }
            else
            {
                int count = workbook.NumberOfSheets;

                for (int i = 0; i < count; ++i)
                {
                    ISheet sheet = workbook.GetSheetAt(i);
                    string sheetName = sheet.SheetName;
                    List<List<string>> sheetContent = this.GetSheetContent(sheet, startRowDictionary[sheetName], endRowDictionary[sheetName]);
                    result.Add(sheetName, sheetContent);
                }
                return result;
            }
        }

        /// <summary>
        /// 根据规则取得sheet页的数据表
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rule"></param>
        /// <returns></returns>
        private List<DataTable> GetDataTable(ISheet sheet, SheetRule rule)
        {
            if (rule != null && sheet != null)
            {
                return rule.ParseModel == 0 ? this.ParseExcelToDataTableByModel0(rule, sheet) : this.ParseExcelToDataTableByModel1(rule, sheet);
            }
            else
            {
                return null;
            }
        }


        private List<DataTable> ParseExcelToDataTableByModel0(SheetRule rule, ISheet sheet)
        {
            return this.ParseRegularTable( rule, sheet);
        }

        private List<DataTable> ParseRegularTable(SheetRule rule, ISheet sheet)
        {
            List<DataTable> dataTables = new List<DataTable>();
            string tableName = sheet.SheetName;
            if(!string.IsNullOrEmpty(rule.DbTableName))
            {
                tableName = rule.DbTableName;
            }
            DataTable dataTable = new DataTable(tableName);
            dataTables.Add(dataTable);

            List<IRow> rows;
            IRow rowHead = null;
            if (rule.RowHeadNum == rule.RowHeadEndNum)
            {
                rows = ExcelUtil.GetHeadNotEmptyRows(rule.RowHeadNum, rule.RowHeadEndNum, sheet);
                if (rows == null || rows.Count < 1)
                {
                    return dataTables;
                }

                this.SetHeadColumn(rows[0],rule, dataTable);
            }
            else
            {
                rows = ExcelUtil.GetHeadNotEmptyRows(rule.RowHeadNum, rule.RowHeadEndNum, sheet);
                if (rows == null || rows.Count < 1)
                {
                    return dataTables;
                }

                foreach (var row in rows)
                {

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell != null)
                        {
                            string value = ExcelUtil.GetCellStringValue(cell);
                            value = value.Replace(".", "$$$");
                            cell.SetCellValue(value);

                            int rowIndex = cell.RowIndex;
                            if (ExcelUtil.IsMergedRegion(sheet, cell))
                            {
                                int count = this.GetCellInfo(rowIndex, i, sheet);
                                if (count > 0)
                                {
                                    for (int j = 0; j < count - 1; ++j)
                                    {
                                        ICell cell2 = row.GetCell(i + j + 1);
                                        cell2.SetCellValue(value);
                                    }

                                    i += count - 1;
                                }
                            }
                        }
                    }
                }

                rowHead = rows[0];

                for (int j = 1; j < rows.Count; ++j)
                {
                    IRow row = rows[j];

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell != null && !string.IsNullOrEmpty(ExcelUtil.GetCellStringValue(cell)))
                        {
                            string cellValue = ExcelUtil.GetCellStringValue(rowHead.GetCell(i));
                            rowHead.GetCell(i).SetCellValue(cellValue + "." + ExcelUtil.GetCellStringValue(cell));
                        }
                    }
                }

                this.SetHeadColumn(rowHead,rule, dataTable);
            }


            rows = ExcelUtil.GetNotEmptyRows(rule.StartRowNum, sheet);
            int rownum = 0;
            int colnum = 0;
            try
            {
                foreach (var row in rows)
                {
                    rownum = row.RowNum;
                    colnum = row.PhysicalNumberOfCells;
                    if (rule.EndRowNum > 0 && row.RowNum > rule.EndRowNum)
                    {
                        break;
                    }
                    DataRow dataRow = dataTable.NewRow();
                    this.SetDataRowWithExcelRow(row, dataRow);
                    dataTable.Rows.Add(dataRow);
                }
            }
            catch (Exception)
            {

                throw new Exception("第几行"+ rownum+"行,"+colnum+"列数据有问题!");
            }

    

            return dataTables;
        }

        /// <summary>
        /// 转换不规则excel表为数据库表(暂时保留备用)
        /// 所有数据最后保存成单条记录
        /// </summary>
        /// <param name="rule"></param>
        /// <param name="sheet"></param>
        /// <param name="table"></param>
        private void ParseUnRegularTable(SheetRule rule, ISheet sheet, DataTable table)
        {
            this.SetHeadColumn(rule, table);
            List<IRow> rows = ExcelUtil.GetNotEmptyRows(rule.StartRowNum, sheet);
            DataRow dataRow = table.NewRow();
            foreach (var row in rows)
            {
                if(rule.EndRowNum >0 && row.RowNum> rule.EndRowNum)
                {
                    break;
                }
                this.SetDataRowWithExcelRow(rule, row, dataRow);
            }
            table.Rows.Add(dataRow);
        }

        private List<DataTable> ParseExcelToDataTableByModel1(SheetRule rule, ISheet sheet)
        {
            List<DataTable> tableList = new List<DataTable>();
            string tableName = sheet.SheetName;
            if (!string.IsNullOrEmpty(rule.DbTableName))
            {
                tableName = rule.DbTableName;
            }
            DataTable table = new DataTable(tableName);
            tableList.Add(table);
            table.Columns.Clear();
            table.Columns.Add(new DataColumn("colindex"));//COLINDEX
            table.Columns.Add(new DataColumn("rowindex"));//ROWINDEX
            table.Columns.Add(new DataColumn("mergerowcount"));//MERGEROWCOUNT
            table.Columns.Add(new DataColumn("mergecolcount"));//MERGECOLCOUNT
            table.Columns.Add(new DataColumn("datavalue"));//DATAVALUE
            List<IRow> rows = ExcelUtil.GetNotEmptyRows(rule.StartRowNum, sheet);

            foreach (var row in rows)
            {

                for (int i = 0; i < row.LastCellNum; ++i)
                {
                    ICell cell = row.GetCell(i);
                    if (cell != null)
                    {
                        int rowIndex = cell.RowIndex;
                        if (ExcelUtil.IsMergedRegion(sheet, cell))
                        {
                            int count = this.GetAndSaveCellInfo(table, rowIndex, i, sheet);
                            if (count > 0)
                            {
                                i += count - 1;
                            }
                        }
                        else
                        {
                            DataRow dataRow = table.NewRow();
                            dataRow["colindex"] = i.ToString();
                            dataRow["rowindex"] = rowIndex.ToString();
                            dataRow["mergerowcount"] = "1";
                            dataRow["mergecolcount"] = "1";
                            dataRow["datavalue"] = ExcelUtil.GetCellStringValue(cell);
                            table.Rows.Add(dataRow);
                        }
                    }
                }
            }

            return tableList;
        }

        private void SetDataRowWithExcelRow(IRow row, DataRow dataRow)
        {
            
            if (row != null)
            {
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell != null&& !cell.IsNullOrEmpty())
                    {
                        string value = ExcelUtil.GetCellStringValue(cell);
                        dataRow[i] = value;
                    }
                }
            }
        }

        private void SetDataRowWithExcelRow(SheetRule rule, IRow row, DataRow dataRow)
        {
            if (rule != null && row != null)
            {
                foreach (var columnRule in rule.ColumRules)
                {
                    if(columnRule.RowNum > 0 && row.RowNum != columnRule.RowNum)
                    {
                        continue;
                    }

                    ICell cell = row.GetCell(columnRule.ColumnNum);
                    if (cell != null)
                    {
                        string value = ExcelUtil.GetCellStringValue(cell);
                        string colName = columnRule.ColumnName;
                        dataRow[colName] = value;
                    }
                }
            }
        }

        /// <summary>
        /// 根据规则列表设定数据表列
        /// </summary>
        /// <param name="rule"></param>
        /// <param name="table"></param>
        private void SetHeadColumn(SheetRule rule, DataTable table)
        {
            if (table != null)
            {
                table.Columns.Clear();
                for (int i = 0; i < rule.ColumRules.Count; i++)
                {
                    ColumnRule colRule = rule.ColumRules[i];
                    table.Columns.Add(new DataColumn(colRule.ColumnName));
                }
            }
        }

        /// <summary>
        /// 根据excel的标题行设定数据表列
        /// </summary>
        /// <param name="row"></param>
        /// <param name="rule"></param>
        /// <param name="table"></param>
        private void SetHeadColumn(IRow row, SheetRule rule, DataTable table)
        {
            if (table != null)
            {
                if (row != null)
                {
                    table.Columns.Clear();

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell != null && !string.IsNullOrEmpty(ExcelUtil.GetCellStringValue(cell)))
                        {
                            string columnName = ExcelUtil.GetCellStringValue(cell);
                            var columnRule = rule.ColumRules.Find(t => t.ColumnName.Trim().Equals(columnName.Trim()));
                            if(columnRule != null)
                            {
                                if(!string.IsNullOrEmpty(columnRule.DbColumnName))
                                {
                                    columnName = columnRule.DbColumnName;
                                }
                            }
                            table.Columns.Add(new DataColumn(columnName));
                        }
                    }
                }
            }
        }


        /// <summary>
        /// 取得并保存合并单元格数据
        /// </summary>
        /// <param name="table"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private int GetAndSaveCellInfo(DataTable table, int rowIndex, int colIndex, ISheet sheet)
        {
            DataRow dataRow = table.NewRow();
            int sheetmergerCount = sheet.NumMergedRegions;
            int colspan = 0;

            for (int i = sheetmergerCount - 1; i >= 0; --i)
            {
                CellRangeAddress ca = sheet.MergedRegions[i];
                if (ca != null)
                {
                    int firstC = ca.FirstColumn;
                    int lastC = ca.LastColumn;
                    int firstR = ca.FirstRow;
                    int lastR = ca.LastRow;
                    if (firstC == colIndex && firstR == rowIndex)
                    {
                        int rowspan = lastR - firstR + 1;
                        colspan = lastC - firstC + 1;
                        IRow row = sheet.GetRow(firstR);
                        ICell cell = row.GetCell(firstC);
                        dataRow["COLINDEX"] = firstC.ToString();
                        dataRow["ROWINDEX"] = firstR.ToString();
                        dataRow["MERGEROWCOUNT"] = rowspan.ToString();
                        dataRow["MERGECOLCOUNT"] = colspan.ToString();
                        dataRow["DATAVALUE"] = ExcelUtil.GetCellStringValue(cell);
                        table.Rows.Add(dataRow);
                        break;
                    }
                }
            }
            return colspan;
        }

        /// <summary>
        /// 取得当前合并单元格的跨列数
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private int GetCellInfo(int rowIndex, int colIndex, ISheet sheet)
        {
            int sheetmergerCount = sheet.NumMergedRegions;
            int colspan = 0;

            for (int i = sheetmergerCount - 1; i >= 0; --i)
            {
                CellRangeAddress ca = sheet.MergedRegions[i];
                if (ca != null)
                {
                    int firstC = ca.FirstColumn;
                    int lastC = ca.LastColumn;
                    int firstR = ca.FirstRow;
                    if (firstC == colIndex && firstR == rowIndex)
                    {
                        colspan = lastC - firstC + 1;
                        break;
                    }
                }
            }
            return colspan;
        }

    }
}
